from sqlalchemy import UniqueConstraint, PrimaryKeyConstraint, Index, Sequence
from sqlalchemy.dialects.postgresql import TIMESTAMP

from tzq.extensions import db


class UploadedFile(db.Model):
    __tablename__ = 'cs_uploaded_file_t'
    id = db.Column(db.Integer, primary_key=True)
    filename = db.Column(db.String(255), nullable=False)
    content = db.Column(db.Text, nullable=False)
    upload_time = db.Column(TIMESTAMP(timezone=False, precision=0), nullable=False,
                            server_default=db.func.clock_timestamp(), comment='上传时间')
    file_size = db.Column(db.BigInteger)

    def __repr__(self):
        return f"<SQLFile {self.filename}>"

    def to_dict(self):
        return {
            "id": self.id,
            "filename": self.filename,
            "content": self.content,
            "upload_time": self.upload_time.strftime('%Y-%m-%d %H:%M:%S'),
            "file_size": self.file_size,
        }


class SqlFileAnalysis(db.Model):
    """ sql文件分析表 """
    __tablename__ = 'sql_file_analysis_tool_t'  # 数据库表名
    __table_args__ = (
        # 主键约束
        PrimaryKeyConstraint('sql_file_analysis_tool_id', name='sql_file_analysis_tool_pk', comment='主键约束'),
        # 唯一约束， SQLAlchemy 实现联合唯一约束
        UniqueConstraint('id', 'file_name', 'sql_num', name='uc_sql_file_analysis_tool_1',
                         comment='唯一约束：id(根据文件ID)、file_name(文件名)、sql_num(SQL序号)来进行唯一约束。'),
        # 唯一索引
        Index('uidx_sql_file_analysis_tool_1', 'id', 'file_name', 'sql_num', unique=True),
        # 表注释
        {'comment': 'sql文件分析表'},
    )
    sql_file_analysis_tool_id = db.Column(
        db.BigInteger,  # 字段类型
        Sequence('sql_file_analysis_tool_s'),  # 序列名称
        primary_key=True,  # 主键
        server_default=Sequence('sql_file_analysis_tool_s').next_value(),  # 默认值
        nullable=False,  # 非空约束。False为禁止为空
        comment='主键ID'
    )
    id = db.Column(db.BigInteger, nullable=False, comment='文件ID')
    file_name = db.Column(db.String(500), nullable=False, comment='文件名')
    file_content = db.Column(db.Text, comment='文件内容')
    sql_num = db.Column(db.BigInteger, comment='SQL序号')
    sql_type = db.Column(db.Text, comment='SQL类型')
    line_numbers = db.Column(db.BigInteger, comment='行号')
    line_count = db.Column(db.BigInteger, comment='SQL行数')
    each_sql_content = db.Column(db.Text, comment='SQL内容')
    sql_detail = db.Column(db.Text, comment='SQL明细')
    table_name = db.Column(db.Text, comment='表名')
    index_name = db.Column(db.Text, comment='索引名')
    has_last_update_date = db.Column(db.Text, comment='更新last_update_date')
    update_has_where_condition = db.Column(db.Text, comment='update有where条件')
    delete_has_where_condition = db.Column(db.Text, comment='delete有where条件')
    check_chinese_semicolon = db.Column(db.Text, comment='无中文分号')
    first_5_lines_has_search_path = db.Column(db.Text, comment='文件内容前5行是否有search_path')

    def to_dict(self):
        return {
            "sql_file_analysis_tool_id": self.sql_file_analysis_tool_id,
            "id": self.id,
            "file_name": self.file_name,
            "file_content": self.file_content,
            "sql_num": self.sql_num,
            "sql_type": self.sql_type,
            "line_numbers": self.line_numbers,
            "line_count": self.line_count,
            "each_sql_content": self.each_sql_content,
            "sql_detail": self.sql_detail,
            "table_name": self.table_name,
            "index_name": self.index_name,
            "has_last_update_date": self.has_last_update_date,
            "update_has_where_condition": self.update_has_where_condition,
            "delete_has_where_condition": self.delete_has_where_condition,
            "check_chinese_semicolon": self.check_chinese_semicolon,
            "first_5_lines_has_search_path": self.first_5_lines_has_search_path
        }
